﻿
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "sqlite3.h"

#define bool char
#define true 1
#define false 0

//测试学生数据结构体
typedef struct TestStudent
{
	char name[20];               //姓名
	int age;                     //年龄            
};
//! INSERT INTO "main"."Student_Blob" ("ID", "StudentBlob", "ROWID") VALUES (1, X'D5C5C8FD00CCCCCCCCCCCCCCCCCCCCCCCCCCCCCC13000000', 1);
//函数功能：向表中插入BLOB数据
//参数：    sqlite3 *db[IN]                               -- 数据库操作指针
//          const TestInfoStruct* info[IN]                -- 要插入的信息
//          const int recordId[IN]                        -- 记录ID
//返回值：  bool                                          -- 函数执行成功，则返回true；否则返回false
bool InsertBlobData(sqlite3* db, const struct TestStudent student, const int recordId)
{
	char* zErrMsg = 0;
	char sql[1000];
	int rc;

	sqlite3_stmt* stmt;
	sprintf(sql, "insert into Student_Blob values('%d',?);", recordId);
	sqlite3_prepare(db, sql, strlen(sql), &stmt, 0);
	{
		sqlite3_bind_blob(stmt, 1, &student, sizeof(struct TestStudent), NULL);
		sqlite3_step(stmt);
	}

	sqlite3_finalize(stmt);
	return true;
}
bool InsertBlobData2(sqlite3* db, struct TestStudent student, const int recordId)
{
	char* zErrMsg = 0;
	char sql[1000];
	int rc;

	sqlite3_stmt* stmt;
	//sprintf(sql, "insert into Student_Blob values('%d',?);", recordId);
	char sql2[255] = "insert into Student_Blob values(?,?)";
	sqlite3_prepare(db, sql2, strlen(sql2), &stmt, 0);
	{
		sqlite3_reset(stmt);
		sqlite3_bind_int(stmt, 1, rand());
		sqlite3_bind_blob(stmt, 2, &student, sizeof(struct TestStudent), NULL);
		sqlite3_step(stmt);
	}
	{
		sqlite3_reset(stmt);
		sqlite3_bind_int(stmt, 1, rand());
		student.age = 600;
		strcpy(student.name, "zmj");
		sqlite3_bind_blob(stmt, 2, &student, sizeof(struct TestStudent), NULL);
		sqlite3_step(stmt);
	}
	sqlite3_finalize(stmt);
	return true;
}

//函数功能：向表中插入BLOB数据
//参数：    sqlite3 *db[IN]                         -- 数据库操作指针
//          TestInfoStruct* info[IN]                -- 要插入的信息
//返回值：  bool                                    -- 函数执行成功，则返回true；否则返回false
bool SelectBlobData(sqlite3* db)
{
	char* zErrMsg = 0;
	char sql[1000];
	int rc;

	sqlite3_stmt* stmt;
	//读取数据
	sqlite3_prepare(db, "select * from Student_Blob;", strlen("select * from Student_Blob;"), &stmt, 0);
	int result = sqlite3_step(stmt);
	int id = 0, len = 0;
	while (result == SQLITE_ROW)
	{
		char cStudentId[20];
		struct TestStudent tempStudent;
		printf( "查询到一条记录" );
		id = sqlite3_column_int(stmt, 0);
		printf( "记录编号:%d" , id );
		const void* pReadBolbData = sqlite3_column_blob(stmt, 1);
		len = sqlite3_column_bytes(stmt, 1);

		memcpy(&tempStudent, pReadBolbData, len);
		printf( "姓名=%s" , &tempStudent.name );
		printf( "年龄=%s" , &tempStudent.age );
		result = sqlite3_step(stmt);
	}
	sqlite3_finalize(stmt);
	return true;
}
bool conbine2localfile(sqlite3* db, char* pfile)
{
	 char* buffer=NULL;
	
	sqlite3_stmt* stmt;
	//读取数据
	char sqlstr[256]="SELECT end FROM t_opusstream where filename='http://127.0.0.1/pcm/daomp3_26.opus' and end >0  LIMIT 1";
	int maxcount=0;
	//sprintf(sqlstr, "SELECT end FROM t_opusstream where filename='%s' and end >0  LIMIT 1", pfile);
	sqlite3_prepare(db, sqlstr, strlen(sqlstr), &stmt, 0);
	//	sqlite3_reset(stmt);
	int result = sqlite3_step(stmt);
	int ccount = 0, len = 0,offset=0;
	if (result == SQLITE_ROW)
	{
		maxcount = sqlite3_column_int(stmt, 0);
	}
	if (maxcount > 0)
	{
		buffer = (char*)malloc(maxcount);
		memset(buffer, 0, maxcount);
	}
	sprintf(sqlstr, "SELECT offset,count,raw FROM t_opusstream where filename='%s' ORDER BY t_opusstream.offset asc", pfile);
	sqlite3_prepare(db, sqlstr, strlen(sqlstr), &stmt, 0);
//	sqlite3_reset(stmt);
	 result = sqlite3_step(stmt);
	 ccount = 0, len = 0;
	 int iii = 0;
	while (result == SQLITE_ROW)
	{
		char cStudentId[20];
		printf("查询到一条记录");
		offset = sqlite3_column_int(stmt, 0);
		ccount = sqlite3_column_int(stmt, 1);
		printf("ccount:%d", ccount);
		const void* pReadBolbData = sqlite3_column_blob(stmt, 2);
		len = sqlite3_column_bytes(stmt, 2);
		if (maxcount > 0)
		{
			memcpy(buffer+offset, pReadBolbData, len);
		}
		iii++;
		result = sqlite3_step(stmt);
	}
	sqlite3_finalize(stmt);

	FILE* outfile;
	outfile = fopen("out.opus", "wb");
	if (outfile == NULL)
	{
		exit(1);
	}
	fwrite(buffer, maxcount, 1, outfile);
	fclose(outfile);
	return true;
}

int main(int argc, char* argv[])
{
	sqlite3* pDataBase = NULL;
	int rc = sqlite3_open("localstorage.db", &pDataBase);
	if (rc != 0)
	{
		printf("创建数据库失败");
	}

	//操作数据库
	sqlite3* db;
	char* zErrMsg = 0;
	char sql[1000];
//	SelectBlobData(pDataBase);
	conbine2localfile(pDataBase,"http://127.0.0.1/pcm/daomp3_26.opus");
	//{
	//	printf( "数据库打开成功!" );

	//	//写BLOB数据
	//	struct TestStudent student1;

	//	strcpy(student1.name, "张三");
	//	student1.age = 19;
	//	InsertBlobData2(pDataBase, student1, 1);

	//	struct TestStudent student2;
	//	strcpy(student2.name, "李四");
	//	student2.age = 18;
	//	InsertBlobData(pDataBase, student2, 2);

	//	//读取数据
	//	SelectBlobData(pDataBase);

	//	//关闭数据库
	//	int iRet = sqlite3_close(pDataBase);
	//	if (0 == iRet)
	//	{
	//		printf( "数据库关闭成功!" );
	//	}
	//}

	getchar();
	return 0;


	system("pause");
	return 0;
}

